package com.wg.msshopsp.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wg.msshopsp.entity.Review;
import com.wg.msshopsp.entity.ReviewForm;
import com.wg.msshopsp.entity.ReviewUserDTO;
import com.wg.msshopsp.entity.ReviewUserShopDto;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ReviewMapper extends BaseMapper<Review> {
    @Select("SELECT " +
            "od.sku, " +  // 新增：订单详情的sku
            "us.reviewCount, " +
            "us.imgCount, " +
            "us.reviewAgainCount, " +
            "u.avatar, " +
            "u.nc, " +
            "r.create_time AS time, " +
            "r.content " +
            "FROM review r " +
            "JOIN orderdetails od ON r.order_item_id = od.id " +  // 关联订单详情表
            "JOIN `user` u ON r.user_id = u.id " +
            "JOIN ( " +
            "    SELECT " +
            "        r_inner.user_id, " +
            "        COUNT(*) AS reviewCount, " +
            "        COUNT(r_inner.img) AS imgCount, " +
            "        CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS reviewAgainCount " +
            "    FROM review r_inner " +
            "    WHERE r_inner.product_id = #{productId} " +  // 筛选目标商品
            "    GROUP BY r_inner.user_id " +
            ") us ON r.user_id = us.user_id " +
            "WHERE r.product_id = #{productId}")  // 主查询筛选商品
    List<ReviewUserDTO> getReviewUserInfo(@Param("productId") Integer productId);


    @Select("SELECT " +
            "us.reviewCount, " +
            "us.imgCount, " +
            "us.reviewAgainCount, " +
            "u.avatar, " +
            "u.nc, " +
            "r.create_time AS time, " +
            "r.content " +
            "FROM review r " +
            "JOIN product p ON r.product_id = p.id " +
            "JOIN `user` u ON r.user_id = u.id " +
            "JOIN ( " +
            "    SELECT " +
            "        r_inner.user_id, " +
            "        COUNT(*) AS reviewCount, " +  // 用户总评论数
            "        COUNT(r_inner.img) AS imgCount, " +
            "        CASE WHEN COUNT(*) >= 2 THEN 1 ELSE 0 END AS reviewAgainCount " +
            "    FROM review r_inner " +
            "    JOIN product p_inner ON r_inner.product_id = p_inner.id " +
            "    WHERE p_inner.shop_id = #{shopId} " +
            "    GROUP BY r_inner.user_id " +
            ") us ON r.user_id = us.user_id " +
            "WHERE p.shop_id = #{shopId} " +
            "ORDER BY r.create_time DESC")
    List<ReviewUserDTO> getReviewUserInfoByTime(@Param("shopId") Integer shopId);




    //==================================================
    @Insert("INSERT INTO review (" +
            "order_item_id, user_id, product_id, score, content, img, create_time" +
            ") " +
            "SELECT " +
            "#{form.orderItemId}, #{userId}, od.product_id, #{form.score}, #{form.content}, #{form.img}, NOW() " +
            "FROM orderdetails od " +
            "JOIN ordertb o ON od.order_id = o.id " +
            "WHERE " +
            "od.id = #{form.orderItemId} " +  // 匹配订单详情ID
            "AND o.user_id = #{userId} " +    // 验证订单归属当前用户
            "AND o.status = 3 " +             // 验证订单状态为"已完成"
            "AND EXISTS (SELECT 1 FROM orderdetails WHERE id = #{form.orderItemId})")  // 验证订单详情存在
    int insertReviewWithValidation(
            @Param("form") ReviewForm form,
            @Param("userId") Integer userId
    );
    @Select("SELECT " +
            "r.id, " +
            "r.score, " +
            "r.content, " +
            "r.create_time AS time, " +
            "r.user_id, " +
            "u.avatar, " +
            "u.nc, " +
            "od.sku, " +  // 订单详情的SKU信息
            "COALESCE(rs.text, '暂无回复') AS reply_content " +  // 商家回复内容
            "FROM review r " +
            // 关联订单详情表获取SKU信息
            "JOIN orderdetails od ON r.order_item_id = od.id " +
            // 关联用户表获取用户信息
            "JOIN `user` u ON r.user_id = u.id " +
            // 左连接商家回复表（无回复时显示默认文本）
            "LEFT JOIN reviewshop rs ON r.id = rs.review_id " +
            // 筛选指定商品的评论
            "WHERE r.product_id = #{productId} " +
            // 按评论时间倒序排列
            "ORDER BY r.create_time DESC")
    List<ReviewUserShopDto> getReviewUserShopInfo(@Param("productId") Integer productId);
}
